﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.VLine.InputDto._0322;
using HCQ2_Model.VLine.OutputDto;
using HCQ2_Model.VR.Model;
using HCQ2_Model.VR.Params;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRFounderApplyDAL: IT_VRFounderApplyDAL
    {
        /// <summary>
        ///  获取合伙人申请列表
        /// </summary>
        /// <param name="applyStatus"></param>
        /// <returns></returns>
        public List<UserMessageResult> GetUserMessageList(int applyStatus,int page,int rows,out int total)
        {
            sb?.Clear();
            //记录数
            string totalPram = "SELECT COUNT(0)AS num from ";
            sb.Append($@"(SELECT ROW_NUMBER() OVER(ORDER BY apply_date DESC)AS num,user_id,CONVERT(varchar(100),apply_date,120) AS apply_date,folder_id,folder_name,apply_status,ref_name,user_level,
approve_name,CONVERT(varchar(100),approve_date,120) AS approve_date,approve_note,update_name,CONVERT(varchar(100),apply_date,120) AS update_date FROM dbo.T_VRFounderApply WHERE 1=1 ");
            if (applyStatus > -1)
            {
                if(applyStatus==10)
                    sb.Append($" AND apply_status>0 ");
                else
                    sb.Append($" AND apply_status={applyStatus} ");
            }
            sb.Append($@")a INNER JOIN (SELECT user_id,user_name FROM dbo.T_User)u ON u.user_id = a.user_id");
            total = Helper.ToInt(SqlHelper.ExecuteScalar(totalPram+sb.ToString()));
            return SqlHelper.ExecuteList<UserMessageResult>($"SELECT TOP {rows} a.*,u.user_name FROM " + sb.ToString() + $" WHERE a.num>{(page - 1) * rows} ORDER BY a.apply_date DESC ");
        }

        //*******************************************接口************************************************
        /// <summary>
        ///  获取合伙人列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<PartnerOuput> GetPartnerList(PartnerInput param)
        {
            sb?.Clear();
            sb.Append($@"SELECT a.user_id,folder_id,user_level,apply_date,user_name,u.user_phone,folder_name,
            apply_status=(CASE WHEN apply_status>1 THEN '无效' ELSE '有效' END),
		    u.userid,u.dd_line,u.user_note,u.unit_name,isnull(f.du_friend,100)as du_friend,d.*,
            approve_name,approve_date,approve_note,ref_name,u.login_name,u.user_img FROM 
            (SELECT user_id,folder_id,user_level,CONVERT(varchar(100),apply_date,120) AS apply_date,
            apply_phone AS user_phone,folder_name,apply_status,approve_name,CONVERT(varchar(100),approve_date,120) AS approve_date,
            approve_note,ref_name FROM T_VRFounderApply WHERE apply_status<>0 AND folder_id={param.folder_id} ");
            //无效
            if (param.mess_status ==0) 
                sb.Append($" AND apply_status<>1 ");
            //有效
            if (param.mess_status == 1)
                sb.Append($" AND apply_status=1 ");
            sb.Append(")a INNER JOIN(SELECT user_id,user_name,user_guid AS userid,login_name,user_img,user_phone,user_status as dd_line,user_note,user_unit AS unit_name FROM dbo.T_User WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (user_name LIKE '%{param.keyword}%' OR user_phone LIKE '%{param.keyword}%' )");
            sb.Append($@")u on a.user_id=u.user_id LEFT JOIN (SELECT du_id,hide_mobile,user_type,du_name,city,sense=dbo.GetDistance({param.gps_x},{param.gps_y},gps_x,gps_y) FROM dbo.DD_User WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.du_name))
                sb.Append($" AND du_name LIKE '%{param.du_name}%' ");
            sb.Append($@")d on a.user_id=d.du_id left join
		    (SELECT du_friend=1,case when dd_id_1={param.user_id} THEN dd_id_2 else dd_id_1 end AS dd_id FROM dbo.DD_1 with(nolock) 
		    WHERE dd_id_1={param.user_id}  OR dd_id_2={param.user_id} UNION SELECT du_friend=2,case when dd_id_1={param.user_id} THEN dd_id_2 else dd_id_1 end AS dd_id FROM dbo.DD_2 a with(nolock) 
		    WHERE dd_id_1={param.user_id} OR dd_id_2={param.user_id})f on a.user_id=f.dd_id ");
            if (param.order_type == 1)
                sb.Append(" ORDER BY a.apply_date DESC;");
            else
                sb.Append(" ORDER BY d.sense ASC;");
            return SqlHelper.ExecuteList<PartnerOuput>(sb.ToString());
        }
        /// <summary>
        ///  获取下线用户列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<PartnerOuput> GetOffLineUserList(OffLineUserParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT a.user_id,userid=(SELECT TOP 1 user_guid FROM dbo.T_User WHERE user_id=a.user_id),folder_id,user_level,apply_date,user_name,folder_name,apply_status=(CASE WHEN apply_status>1 THEN '无效' ELSE '有效' END),
				(CASE WHEN d.hide_mobile>0 THEN u.user_phone ELSE '' END)AS user_phone,isnull(f.du_friend,100)as du_friend,
                approve_name,approve_date,approve_note,ref_name,u.login_name,u.user_img,u.dd_line,u.user_note,d.* FROM 
                (SELECT user_id,folder_id,user_level,CONVERT(varchar(100),apply_date,120) AS apply_date,apply_name AS user_name,
                apply_phone AS user_phone,folder_name,apply_status,approve_name,CONVERT(varchar(100),approve_date,120) AS approve_date,
                approve_note,ref_name FROM T_VRFounderApply WHERE apply_status<>0 AND folder_id={param.folder_id}  AND ref_id={param.user_id} ");
            if (param.mess_status > -1)
                sb.Append($" AND apply_status={param.mess_status} ");
            sb.Append($@")a INNER JOIN(SELECT user_id,login_name,user_img,user_phone,user_status as dd_line,user_note FROM dbo.T_User where 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (user_name LIKE '%{param.keyword}%' OR user_phone LIKE '%{param.keyword}%') ");
            sb.Append($@")u ON u.user_id = a.user_id INNER JOIN(SELECT du_id,hide_mobile,user_type,du_name,city,sense=dbo.GetDistance({param.gps_x},{param.gps_y},gps_x,gps_y) FROM dbo.DD_User WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.du_name))
                sb.Append($" AND du_name LIKE '%{param.du_name}%' ");
             sb.Append($@")d on a.user_id=d.du_id left join(SELECT du_friend=1,case when dd_id_1={param.user_id} THEN dd_id_2 else dd_id_1 end AS dd_id FROM dbo.DD_1 with(nolock) 
                WHERE dd_id_1={param.user_id}  OR dd_id_2={param.user_id} UNION SELECT du_friend=2,case when dd_id_1={param.user_id}THEN dd_id_2 else dd_id_1 end AS dd_id FROM dbo.DD_2 a with(nolock) 
                WHERE dd_id_1={param.user_id} OR dd_id_2={param.user_id})f on a.user_id=f.dd_id ");
            if (param.order_type == 1)
                sb.Append(" ORDER BY a.approve_date DESC;");
            else
                sb.Append(" ORDER BY d.sense ASC;");
            return SqlHelper.ExecuteList<PartnerOuput>(sb.ToString());
        }
        /// <summary>
        ///  获取积分明细
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<ScoreDetialResult> GetScoreDetial(OffLineUserParam param)
        {
            sb?.Clear();
            sb.Append($@"SELECT l.*,f.folder_name FROM  (SELECT score_num,share_id,share_name,pay_name,order_code,folder_id,file_name,
             file_code,pay_money,CONVERT(varchar(100),create_date,120) AS create_date FROM dbo.T_VRUserIntegralLog  WHERE folder_id={param.folder_id} AND user_id={param.user_id})l LEFT JOIN
             (SELECT folder_id,folder_name FROM dbo.T_DocumentFolder)f ON l.folder_id=f.folder_id ORDER BY l.create_date DESC; ");
            return SqlHelper.ExecuteList<ScoreDetialResult>(sb.ToString());
        }
    }
}